package com.leeh.jsontest;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import java.util.ArrayList;
import java.util.List;

/**
 * Created by 22847 on 2017/4/16.
 */

public class DBManager {

    /**
     * 查询老师课表
     *
     * @param db           数据库名
     * @param term         学期
     * @param teacher_id   老师id
     * @param teacher_name 老师姓名
     * @param pattern      格式1或2
     * @return String 类型的网页
     */
    public static String teacherCourseQuery(SQLiteDatabase db, String term, String teacher_id, String teacher_name, String pattern) {
        String sql = "select html from teacher_course where term='" + term + "' and teacher_id='" + teacher_id + "' and teacher_name='" + teacher_name + "' and pattern='" + pattern + "'";
        String result = "";
        Cursor cursor = db.rawQuery(sql, null);
        while (cursor.moveToNext()) {
            result = cursor.getString(cursor.getColumnIndex("html"));
        }
        cursor.close();
        MySqliteHelper.closeDatabase(db);
        return result;
    }

    /**
     * 查询课程课表
     *
     * @param db          数据库名
     * @param term        学期
     * @param course_id   课程编号
     * @param course_name 课程名
     * @ String 类型的网页
     */
    public static String courseTableQuery(SQLiteDatabase db, String term, String course_id, String course_name, String pattern) {
        String sql = "select html from course_table where term='" + term + "' and course_id='" + course_id + "' and course_name='" + course_name + "' and pattern='" + pattern + "'";
        String result = "";
        Cursor cursor = db.rawQuery(sql, null);
        while (cursor.moveToNext()) {
            result = cursor.getString(cursor.getColumnIndex("html"));
        }
        cursor.close();
        MySqliteHelper.closeDatabase(db);
        return result;
    }

    /**
     * 查询教室课表
     *
     * @param db             数据库名称
     * @param term           学期
     * @param pattern        格式1或2
     * @param area           区域
     * @param teach_building 教学楼
     * @return String 类型的网页
     */
    public static String classroomTableQuery(SQLiteDatabase db, String term, String area, String teach_building, String classRoom, String pattern) {
        String sql = "select html from classroom_table where term='" + term + "' and pattern='" + pattern + "' and area='" + area + "' and teach_building='" + teach_building + "' and classroom='" + classRoom + "'";
        String result = "";
        Cursor cursor = db.rawQuery(sql, null);
        while (cursor.moveToNext()) {
            result = cursor.getString(cursor.getColumnIndex("html"));
        }
        cursor.close();
        MySqliteHelper.closeDatabase(db);
        return result;
    }

    /**
     * 查询任选课表
     *
     * @param db   数据库名
     * @param term 学期
     * @param area 区域
     * @return String 类型的网页
     */
    public static String optionalTableQuery(SQLiteDatabase db, String term, String area) {
        String sql = "select html from optional_table where term='" + term + "' and area='" + area + "'";
        String result = "";
        Cursor cursor = db.rawQuery(sql, null);
        while (cursor.moveToNext()) {
            result = cursor.getString(cursor.getColumnIndex("html"));
        }
        cursor.close();
        MySqliteHelper.closeDatabase(db);
        return result;
    }

    /**
     * 向老师课表中插入值
     *
     * @param db           数据库名
     * @param term         学期
     * @param teacher_id   教师id
     * @param teacher_name 教师姓名
     * @param pattern      格式1或2
     * @param html         抓取的静态网页
     */
    public static void insertIntoTeaherCourse(SQLiteDatabase db, String term, String teacher_id, String teacher_name, String pattern, String html) {
        String sql = "insert into teacher_course values(?,?,?,?,?)";
        db.execSQL(sql, new Object[]{term, teacher_id, teacher_name, pattern, html});
        MySqliteHelper.closeDatabase(db);
    }

    /**
     * 向课程课表中插入值
     *
     * @param db          数据库名
     * @param term        学期
     * @param course_id   课程id
     * @param course_name 课程名
     * @param html        抓取的静态网页
     */
    public static void insertIntoCourseTable(SQLiteDatabase db, String term, String course_id, String course_name, String pattern, String html) {
        String sql = "insert into course_table values(?,?,?,?,?)";
        db.execSQL(sql, new Object[]{term, course_id, course_name, pattern, html});
        MySqliteHelper.closeDatabase(db);
    }

    /**
     * 向教室课表中插入值
     *
     * @param db             数据库名
     * @param term           学期
     * @param pattern        格式1或2
     * @param area           校区
     * @param teach_building 教学楼
     * @param html           抓取的静态网页
     */
    public static void insertIntoClassroomTable(SQLiteDatabase db, String term, String area, String teach_building, String classRoom, String pattern, String html) {
        String sql = "insert into classroom_table values(?,?,?,?,?,?)";
        db.execSQL(sql, new Object[]{term, area, teach_building, classRoom, pattern, html});
        MySqliteHelper.closeDatabase(db);
    }

    /**
     * 向“任选课表”中插入值
     *
     * @param db   数据库名
     * @param term 学期
     * @param area 校区
     * @param html 抓取的静态网页
     */
    public static void insertIntoOptionalTable(SQLiteDatabase db, String term, String area, String html) {
        String sql = "insert into optional_table values(?,?,?)";
        db.execSQL(sql, new Object[]{term, area, html});
        MySqliteHelper.closeDatabase(db);
    }

    /**
     * 查询下拉框中所有的课程名
     *
     * @param db 给定数据库对象
     * @return 返回一个list对象，如果有数据，那么其中的数据是SpinnerContent类型的
     */
    public static List<SpinnerContent> getCourseSpinner(SQLiteDatabase db) {
        List<SpinnerContent> result = new ArrayList<SpinnerContent>();
        String sql = "select * from spinner_course";
        Cursor cursor = db.rawQuery(sql, null);
        Log.i("ring", "执行了spinnerCourse中的sql语句");
        while (cursor.moveToNext()) {
            String key = cursor.getString(cursor.getColumnIndex("key"));
            String value = cursor.getString(cursor.getColumnIndex("value"));
            SpinnerContent sc = new SpinnerContent(key, value);
            result.add(sc);
        }
        cursor.close();
        MySqliteHelper.closeDatabase(db);
        return result;
    }

    /**
     * 查询下拉框中所有的老师姓名
     *
     * @param db
     * @return 内容同getCourseSpinner
     */
    public static List<SpinnerContent> getTeacherSpinner(SQLiteDatabase db) {
        List<SpinnerContent> result = new ArrayList<SpinnerContent>();
        String sql = "select * from spinner_teacher";
        Cursor cursor = db.rawQuery(sql, null);
        while (cursor.moveToNext()) {
            String key = cursor.getString(cursor.getColumnIndex("key"));
            String value = cursor.getString(cursor.getColumnIndex("value"));
            SpinnerContent sc = new SpinnerContent(key, value);
            result.add(sc);
        }
        cursor.close();
        MySqliteHelper.closeDatabase(db);
        return result;
    }

    /**
     * 插入键值对到数据库
     *
     * @param db       数据库对象
     * @param coureses
     */
    public static void setCourseSpinner(final SQLiteDatabase db, final List<SpinnerContent> coureses) {
        new Thread() {
            @Override
            public void run() {
                String sql = "insert into spinner_course values(?,?)";
                for (SpinnerContent sc : coureses) {
                    db.execSQL(sql, new Object[]{sc.getKey(), sc.getValue()});
                }
                MySqliteHelper.closeDatabase(db);
            }
        }.start();
    }

    /**
     * 内容和参数同setCourseSpinner
     *
     * @param db
     * @param teachers
     */
    public static void setTeacherSpinner(final SQLiteDatabase db, final List<SpinnerContent> teachers) {
        new Thread() {
            @Override
            public void run() {
                db.beginTransaction();
                String sql = "insert into spinner_teacher values(?,?)";
                Log.i("teee ", sql);
                for (SpinnerContent sc : teachers) {
                    db.execSQL(sql, new Object[]{sc.getKey(), sc.getValue()});
                }
                db.setTransactionSuccessful();
                db.endTransaction();
                MySqliteHelper.closeDatabase(db);
            }
        }.start();
    }


    /**
     * 模糊查询函数
     *
     * @param db    数据库对象
     * @param input 用户输入的字符片段
     * @return 返回所有相关的结果，保存在List中
     */
    public static List<SpinnerContent> teacherSpinnerValueSearch(SQLiteDatabase db, String input) {
        List<SpinnerContent> result = new ArrayList<SpinnerContent>();
        String sql = "select * from spinner_teacher where value like '%" + input + "%'";
        Log.i("teee ", sql);
        Cursor cursor = db.rawQuery(sql, null);
        while (cursor.moveToNext()) {
            String key = cursor.getString(cursor.getColumnIndex("key"));
            String value = cursor.getString(cursor.getColumnIndex("value"));
            SpinnerContent sc = new SpinnerContent(key, value);
            result.add(sc);
        }
        cursor.close();
        MySqliteHelper.closeDatabase(db);
        return result;
    }

    /**
     * 插入学期信息
     *
     * @param db
     * @param terms
     */
    public static void insertIntoTerm(final SQLiteDatabase db, final List<SpinnerContent> terms) {
        new Thread() {
            @Override
            public void run() {
                db.beginTransaction();
                String sql = "insert into spinner_term values(?,?)";
                for (SpinnerContent sc : terms) {
                    db.execSQL(sql, new Object[]{sc.getKey(), sc.getValue()});
                }
                db.setTransactionSuccessful();
                db.endTransaction();
                MySqliteHelper.closeDatabase(db);
            }
        }.start();

    }

    /**
     * 插入校区信息
     *
     * @param db
     * @param campuses
     */
    public static void insertIntoCampus(final SQLiteDatabase db, final List<SpinnerContent> campuses) {
        new Thread() {
            @Override
            public void run() {
                db.beginTransaction();
                String sql = "insert into spinner_campus values(?,?)";
                for (SpinnerContent sc : campuses) {
                    db.execSQL(sql, new Object[]{sc.getKey(), sc.getValue()});
                }
                db.setTransactionSuccessful();
                db.endTransaction();
                MySqliteHelper.closeDatabase(db);
            }
        }.start();
    }

    /**
     * 插入教学楼信息
     *
     * @param db
     * @param buildinges
     */
    public static void insertIntoBuilding(final SQLiteDatabase db, final List<SpinnerContent> buildinges) {
        new Thread() {
            @Override
            public void run() {
                db.beginTransaction();
                String sql = "insert into spinner_building values(?,?)";
                for (SpinnerContent sc : buildinges) {
                    db.execSQL(sql, new Object[]{sc.getKey(), sc.getValue()});
                }
                db.setTransactionSuccessful();
                db.endTransaction();
                MySqliteHelper.closeDatabase(db);
            }
        }.start();
    }

    /**
     * 插入教室信息
     *
     * @param db
     * @param rooms
     */
    public static void insertIntoClassroom(final SQLiteDatabase db, final List<SpinnerContent> rooms) {
        new Thread() {
            @Override
            public void run() {
                db.beginTransaction();
                String sql = "insert into spinner_classroom values(?,?)";
                for (SpinnerContent sc : rooms) {
                    db.execSQL(sql, new Object[]{sc.getKey(), sc.getValue()});
                }
                db.setTransactionSuccessful();
                db.endTransaction();
                MySqliteHelper.closeDatabase(db);
            }
        }.start();
    }

    /**
     * 查询学期
     *
     * @param db
     * @return
     */
    public static List<SpinnerContent> getTermSpinnerContent(SQLiteDatabase db) {
        String querySql = "select * from spinner_term";
        Cursor cursor = db.rawQuery(querySql, null);
        List<SpinnerContent> list = cursorToList(cursor);
        cursor.close();
        MySqliteHelper.closeDatabase(db);
        return list;
    }

    /**
     * 查询校区
     *
     * @param db
     * @return
     */
    public static List<SpinnerContent> getCampusSpinnerContent(SQLiteDatabase db) {
        String querySql = "select * from spinner_campus";
        Cursor cursor = db.rawQuery(querySql, null);
        List<SpinnerContent> list = cursorToList(cursor);
        cursor.close();
        MySqliteHelper.closeDatabase(db);
        return list;
    }

    /**
     * 查询教学楼
     *
     * @param db
     * @return
     */
    public static List<SpinnerContent> getBuildingSpinnerContent(SQLiteDatabase db, String key) {
        String querySql = "select * from spinner_building where key like '" + key + "%'";
        Cursor cursor = db.rawQuery(querySql, null);
        List<SpinnerContent> list = cursorToList(cursor);
        cursor.close();
        MySqliteHelper.closeDatabase(db);
        return list;
    }

    /**
     * 查询教室
     *
     * @param db
     * @return
     */
    public static List<SpinnerContent> getClassroomSpinnerContent(SQLiteDatabase db, String key) {
        String querySql = "select * from spinner_classroom where key like '" + key + "%'";
        Cursor cursor = null;
        cursor = db.rawQuery(querySql, null);
        List<SpinnerContent> list = cursorToList(cursor);
        cursor.close();
        MySqliteHelper.closeDatabase(db);
        return list;
    }

    /**
     * 丛数据库游标中取数据到List中
     *
     * @param cursor
     * @return
     */
    private static List<SpinnerContent> cursorToList(Cursor cursor) {
        List<SpinnerContent> list = new ArrayList<>();
        while (cursor.moveToNext()) {
            SpinnerContent sc = new SpinnerContent(cursor.getString(cursor.getColumnIndex("key")), cursor.getString(cursor.getColumnIndex("value")));
            list.add(sc);
        }
        return list;
    }

}
